/************************************************************************************/
/*  Program name CAPFNDIMP_SECURITY_PROFILE.sql                                     */
/*  Creater:  CHAN KA KEUNG BILLY (PCCW - UniHub)                                   */
/*************   History   *************************                                */
/* 09, MAY 2006 CHAN KA KEUNG BILLY     First created                               */
/************************************************************************************/
SET SERVEROUTPUT ON SIZE 1000000;

ALTER TRIGGER FND_CONCURRENT_PROGRAMS_CT1 DISABLE;

DECLARE
	lv_Prog_ID		VARCHAR2(100)	:=	'CAPFNDIMP_SECURITY_PROFILE';

	lv_Scc_Count	NUMBER			:=	0;
	lv_Err_Count	NUMBER			:=	0;
	lv_Err_Msg		VARCHAR2(500)	:=	'No Error';

	CURSOR Cur_F IS
	SELECT
		*
	FROM
		CRST_FND_SECURITY_PROFILE;

	lv_Next_Seq_ID	NUMBER;

BEGIN

	--  RENAME
	
	FOR C1 IN Cur_F LOOP
					
	
		BEGIN
			
			update per_security_profiles
			set SECURITY_PROFILE_NAME = CAPBRIMP_MAPPING.RenameSTR(SUBSTR(SECURITY_PROFILE_NAME,1,4),SECURITY_PROFILE_NAME)
			WHERE SECURITY_PROFILE_ID = C1.SECURITY_PROFILE_ID;
			
			SELECT	PER_SECURITY_PROFILES_S.NextVal
			INTO	lv_Next_Seq_ID
			FROM	DUAL;
			
			UPDATE CRST_FND_SECURITY_PROFILE
			SET SECURITY_PROFILE_ID_N = lv_Next_Seq_ID
			WHERE SECURITY_PROFILE_ID = C1.SECURITY_PROFILE_ID;

			INSERT INTO PER_SECURITY_PROFILES
			VALUES
			(
			lv_Next_Seq_ID		          ,
			CAPBRIMP_MAPPING.GetBGMapping(C1.BUSINESS_GROUP_ID),
			CAPPOIMP_MAPPING.GetPositionIDMapping(C1.POSITION_ID),
			CAPBRIMP_MAPPING.GetOrgMapping(C1.ORGANIZATION_ID),
			CAPPOIMP_MAPPING.GetPosStructureMapping(C1.POSITION_STRUCTURE_ID),
			CAPPOIMP_MAPPING.GetOrgStructureMapping(C1.ORGANIZATION_STRUCTURE_ID),
			C1.INCLUDE_TOP_ORGANIZATION_FLAG ,
			C1.INCLUDE_TOP_POSITION_FLAG     ,
			C1.SECURITY_PROFILE_NAME         ,
			C1.VIEW_ALL_APPLICANTS_FLAG      ,
			C1.VIEW_ALL_EMPLOYEES_FLAG       ,
			C1.VIEW_ALL_FLAG                 ,
			C1.VIEW_ALL_ORGANIZATIONS_FLAG   ,
			C1.VIEW_ALL_PAYROLLS_FLAG        ,
			C1.VIEW_ALL_POSITIONS_FLAG       ,
			C1.INCLUDE_EXCLUDE_PAYROLL_FLAG  ,
			C1.REPORTING_ORACLE_USERNAME     ,
			C1.REQUEST_ID                    ,
			C1.PROGRAM_APPLICATION_ID        ,
			C1.PROGRAM_ID                    ,
			C1.PROGRAM_UPDATE_DATE           ,
			C1.LAST_UPDATE_DATE              ,
			C1.LAST_UPDATED_BY               ,
			C1.LAST_UPDATE_LOGIN             ,
			C1.CREATED_BY                    ,
			C1.CREATION_DATE                 ,
			C1.ALLOW_GRANTED_USERS_FLAG      ,
			C1.RESTRICT_BY_SUPERVISOR_FLAG   ,
			C1.SUPERVISOR_LEVELS             ,
			C1.EXCLUDE_SECONDARY_ASGS_FLAG   ,
			C1.EXCLUDE_PERSON_FLAG           ,
			C1.NAMED_PERSON_ID               ,
			C1.CUSTOM_RESTRICTION_FLAG       ,
			C1.RESTRICTION_TEXT              ,
			C1.EXCLUDE_BUSINESS_GROUPS_FLAG  ,
			C1.ORG_SECURITY_MODE             ,
			C1.VIEW_ALL_CWK_FLAG             ,
			C1.VIEW_ALL_CONTACTS_FLAG        
			);
		EXCEPTION
			WHEN OTHERS THEN
				DBMS_OUTPUT.Put_Line(SQLERRM);
				lv_Err_Count	:=	lv_Err_Count + 1;
		END;
		lv_Scc_Count	:=	lv_Scc_Count	+	1;
	END LOOP;

	/*BEGIN
		FOR C2 IN Cur_G LOOP
			INSERT INTO CFND_CONCURRENT_PROGRAMS VALUES
			(
				C2.Application_ID,
				C2.Concurrent_Program_ID_N,
				CAPBRIMP_MAPPING.GetOrgMapping(C2.S_ORG_ID),
				CAPBRIMP_MAPPING.GetBGMapping(C2.S_BUSINESS_GROUP_ID)
			);
		END LOOP;
	EXCEPTION
		WHEN OTHERS THEN
			DBMS_OUTPUT.Put_Line(SQLERRM);
			lv_Err_Count	:=	lv_Err_Count + 1;
	END;*/

	IF	lv_Err_Count	>	0	THEN
		ROLLBACK;
	END IF;

	INSERT INTO CAPCNV_LOG(
		PROG_ID,
		PROCESS_DATE,
		RECORD_PROCESSED,
		RECORD_REJECTED,
		RECORD_EXTRACTED,
		ERR_MSG
	) VALUES (
		lv_Prog_ID,
		SYSDATE,
		lv_Scc_Count,
		lv_Err_Count,
		lv_Scc_Count-lv_Err_Count,
		lv_Err_Msg
	);

	COMMIT;
END;
/

ALTER TRIGGER FND_CONCURRENT_PROGRAMS_CT1 ENABLE;
